/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BD;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLNonTransientConnectionException;
import java.sql.Statement;
import org.apache.log4j.Logger;

/**
 *
 * @author Angelo
 */
public class MySqlCreador {
    /*
     * NOTA: PARA QUE FUNCIONE EN DERBY: 
     * NOMBRE DE LA BD: sample
     * NOMBRE DE USUARIO: app
     * CLAVE: app
     */

    //private String driver = "com.mysql.jdbc.Driver";
    String driver = "org.apache.derby.jdbc.ClientDriver";
    private String user;
    private String pw;
    // private final String dataBaseName = "mysql";
    private String host;
    private String connectionURL;
    private String nameNewDB;
    public Connection conexion = null;
    private static Logger logger = Logger.getLogger(MySql.class);

    public MySqlCreador(String user, String pw, String host, String nameNewDB) {
//        log = new LogsMySql();
//        this.user = user;
//        this.pw = pw;
//        this.host = host;
//        this.connectionURL = "jdbc:mysql://" + this.host + "/" + this.dataBaseName;
//        this.nameNewDB = nameNewDB;
        this.user = user;
        this.pw = pw;
        this.host = host;
        this.connectionURL = "jdbc:derby://" + this.host + "/" + nameNewDB;
        System.out.println(connectionURL);
        this.nameNewDB = nameNewDB;
        ModificarConfiguracion();
    }

    private void ModificarConfiguracion() {
        ConfiguracionMySql cfg2;
        CargarConfig cargarCfg = new CargarConfig();
        cfg2 = cargarCfg.LeerArchivo();
        cfg2.setDataBaseName(nameNewDB);
        cargarCfg.CrearArchivo(cfg2);
    }
// ESTE METODO NO SERA USADO CON LA BD DERBY

    public int CrearBD() {
        int res;
        try {
            String sql = "CREATE DATABASE " + nameNewDB + ";";
            PreparedStatement ps = conexion.prepareStatement(sql);

            res = ps.executeUpdate(sql);
            this.ModificarConfiguracion();
        } catch (SQLException ex) {

            if (ex.toString().contains("database exists")) {
                res = -1;
            } else {
                res = 0;
            }
        }catch (NullPointerException ex) {

            if (ex.toString().contains("database exists")) {
                res = -1;
            } else {
                res = 0;
            }
        }
        

        return res;
    }

    public int CrearTablas() {
        int res;
        try {
            //String sql = "USE " + nameNewDB;
//            PreparedStatement ps = conexion.prepareStatement(sql);
//            ps.execute(sql);
            String sql;
            Statement stmt = conexion.createStatement();
//            sql = " CREATE TABLE `Responsable` (\n"
//                    + "  `Id_Responsable` int NOT NULL auto_increment, \n"
//                    + "  `Nombre` varchar(100) not NULL,\n"
//                    + "`Apodo` varchar(45) not NULL,\n"
//                    + "`Email` varchar(150) not NULL UNIQUE,\n"
//                    + "`PathFoto` varchar(256) not NULL,\n"
//                    + "  PRIMARY KEY  (`Id_Responsable`)\n"
//                    + ")  ENGINE = INNODB";

            sql = " CREATE TABLE Responsable ("
                    + "Id_Responsable INTEGER  NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                    + "Nombre varchar(100) NOT NULL,"
                    + "Apodo varchar(45) NOT NULL, "
                    + "Email varchar(150) NOT NULL UNIQUE, "
                    + "PathFoto varchar(256) NOT NULL "
                    + ")";
//            ps = conexion.prepareStatement(sql);
//            ps.execute(sql);
            stmt.executeUpdate(sql);
//            sql = "CREATE TABLE `Categoria` (\n"
//                    + "  `Id_Categoria` int NOT NULL auto_increment, \n"
//                    + "`Nombre` varchar(45) not NULL,\n"
//                    + "`Descripcion` varchar(45) not NULL,\n"
//                    + "  PRIMARY KEY  (`Id_Categoria`)\n"
//                    + ") ENGINE = INNODB";

            sql = "CREATE TABLE Categoria ("
                    + "Id_Categoria INTEGER  NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                    + "Nombre varchar(45) NOT NULL,"
                    + "Descripcion varchar(45) NOT NULL"
                    + ")";
//            ps = conexion.prepareStatement(sql);
//            ps.execute(sql);
            stmt.executeUpdate(sql);
//            sql = "CREATE TABLE `Tarea` (\n"
//                    + "  `Id_Tarea` int NOT NULL auto_increment, \n"
//                    + "`Titulo` varchar(256) not NULL,\n"
//                    + "`Texto` varchar(4000) not NULL,\n"
//                    + "`Prioridad` int not NULL,\n"
//                    + "`Vencimiento` DATETIME not NULL,\n"
//                    + "`Completado` float not NULL,\n"
//                    + "`Id_Responsable` int not NULL,\n"
//                    + "`Id_Categoria` int not NULL,\n"
//                    + "  PRIMARY KEY  (`Id_Tarea`),\n"
//                    + "  index (`Id_Responsable`),\n"
//                    + "  index (`Id_Categoria`),\n"
//                    + "  constraint FOREIGN KEY (`Id_Responsable`) REFERENCES Responsable(`Id_Responsable`)"
//                    + "ON DELETE RESTRICT ON UPDATE CASCADE,\n"
//                    + " constraint FOREIGN KEY (`Id_Categoria`) REFERENCES Categoria(`Id_Categoria`)"
//                    + "ON DELETE RESTRICT ON UPDATE CASCADE\n"
//                    + ")  ENGINE = INNODB";

            sql = "CREATE TABLE Tarea ("
                    + "Id_Tarea INTEGER  NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                    + "Titulo varchar(256) NOT NULL,"
                    + "Texto varchar(4000) NOT NULL,"
                    + "Prioridad INTEGER NOT NULL,"
                    + "Vencimiento TIMESTAMP NOT NULL,"
                    + "Completado float NOT NULL,"
//                    + "Id_Responsable INTEGER NOT NULL constraint tarea_resp_fk references Responsable,"
//                    + "Id_Categoria INTEGER NOT NULL constraint tarea_cate_fk references Categoria"
                      + "Id_Responsable INTEGER NOT NULL,"
                    + "Id_Categoria INTEGER NOT NULL "
                    + ")";
//            ps = conexion.prepareStatement(sql);
//            ps.execute(sql);
            stmt.executeUpdate(sql);
            res = 1;
        } catch (SQLException ex) {
            if (ex.toString().contains("already exists") || ex.toString().contains("ya existe")) {
                res = -1;
            } else {
                res = 0;
            }
        }
        return res;
    }

    public void Conectar() throws MyException {
        try {
            Class.forName(driver);
            conexion = DriverManager.getConnection(connectionURL, user, pw);
        } catch (SQLNonTransientConnectionException e) {
            logger.error("Error de conexion: autenticación no valida o servidor no iniciado", e);
            throw new MyException("Error de conexion: autenticación no valida o servidor no iniciado.", e);
        } catch (SQLException e) {
            logger.error("Error de SQL", e);
            throw new MyException("Error de SQL", e);
        } catch (ClassNotFoundException e) {
            logger.error("Error al cargar Driver.", e);
            throw new MyException("Error al cargar Driver.", e);
        }
    }

    public void Desconectar() throws MyException {
        try {
            conexion.close();
        } catch (SQLException e) {
            logger.error("Error de SQL", e);
            throw new MyException("Error de SQL", e);
        }
    }
}
